﻿using System.Text;
using WebApiMIT3.Database;
using WebApiMIT3.Models;

namespace WebApiMIT3.DatabaseAccessLayer
{
    public class Type
    {
        private readonly string tableName = "Type";
        private readonly string typeID = "TypeID";
        private readonly string typeName = "TypeName";

        public string TableName
        {
            get { return this.tableName; }
        }

        public string TypeID
        {
            get { return this.typeID; }
        }

        public string TypeName
        {
            get { return this.typeName; }
        } 

        public void InsertStatement(DataModel dataModel)
        {
            foreach (var value in dataModel.Type)
            {
                var queryValue = this.SelectStatement(value);
                bool doesExists = !string.IsNullOrEmpty(queryValue);

                if (!doesExists)
                {
                    var builder = new StringBuilder();

                    builder.Append(string.Format("INSERT INTO {0} ", this.tableName));
                    builder.Append(string.Format("({0}) ", this.typeName));
                    builder.Append(string.Format("VALUES ('{0}');", this.ReplaceDBChars(value)));

                    DatabaseConnect.Insert(builder.ToString());
                }
            }
        }

        public string SelectStatement(string value)
        {
            var builder = new StringBuilder();

            builder.Append(string.Format("SELECT {0} ", this.typeName));
            builder.Append(string.Format("FROM {0} ", this.tableName));
            builder.Append(string.Format("WHERE {0}='{1}';", this.typeName, this.ReplaceDBChars(value)));

            return DatabaseConnect.Select(builder.ToString());
        }             

        public void UpdateStatement(string oldValue, string newValue)
        {
            var builder = new StringBuilder();

            builder.Append(string.Format("UPDATE {0} ", this.tableName));
            builder.Append(string.Format("SET {0}='{1}' ", this.typeName, newValue));
            builder.Append(string.Format("WHERE {0}='{1}';", this.typeName, oldValue));

            DatabaseConnect.Update(builder.ToString());
        }

        public int CountStatement()
        {
            var builder = new StringBuilder();

            builder.Append(string.Format("SELECT * FROM {0} ", this.tableName));

            return DatabaseConnect.Count(builder.ToString());
        }

        public void DeleteStatement(string value)
        {
            var builder = new StringBuilder();

            builder.Append(string.Format("DELETE FROM {0} ", this.tableName));
            builder.Append(string.Format("WHERE {0}='{1}';", this.typeName, value));

            DatabaseConnect.Delete(builder.ToString());
        }

        public void DeleteAllStatement()
        {
            var builder = new StringBuilder();

            builder.Append(string.Format("DELETE FROM {0} ", this.tableName));

            DatabaseConnect.Delete(builder.ToString());
        }

        public string SelectIndex(string name)
        {
            var builder = new StringBuilder();

            builder.Append(string.Format("SELECT {0} ", this.typeID));
            builder.Append(string.Format("FROM {0} ", this.tableName));
            builder.Append(string.Format("WHERE {0}='{1}';", this.typeName, name));

            return DatabaseConnect.Select(builder.ToString());
        }
        
        private string ReplaceDBChars(string value)
        {
            return value.Replace("'", "''");
        }
    }
}